﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;

namespace DBManager.Admin
{
    public class ImagesDAL
    {
        #region vong thi
        public static int UpdateStatusRound(string round,string Status)
        {
            //xoa anh doat giai
            string sqlSetStatus = @"update AwardsWeek set Status=" + comm.to_sqlnumber(Status) + " where RoundAwards=" + comm.to_sqlnumber(round);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }
        public static DataTable GetAllRound(string round)
        {
            string dkRound = "";
            if (round != "0")
            {
                dkRound = " and RoundAwards=" + comm.to_sqlnumber(round);
            }
            string sql = @"select AwardsID,title,content,startdate,enddate,status,case status
                            when 0 then N'Chưa sử sụng' 
                            when 1 then N'Đang thi' 
                            when 2 then N'Đang chấm điểm'
                            when 3 then N' thi kết thúc'
                            when 4 then N'Hủy giải' 
                            end as trangthai from AwardsWeek 
                           Where 1=1" + dkRound + " order by RoundAwards desc";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationRound(string id)
        {
            string sql = "select * from AwardsWeek where AwardsID=" + comm.to_sqlnumber(id);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationRoundByRoundAwards(string RoundAwards)
        {
            string sql = "select top 1 * from AwardsWeek where status=1 and RoundAwards=" + comm.to_sqlnumber(RoundAwards);
            return DBUtil.SelectTable(sql);
        }
        public static int AddRound(string Title, string Content, string StartDate, string EndDate, string Status, string RoundAwards)
        {
            //them moi
            string sql = @"INSERT INTO AwardsWeek
                       (Title
                       ,Content
                       ,StartDate
                       ,EndDate
                       ,Status
                       ,RoundAwards)
                        VALUES
                       (" + comm.to_sqltext(Title) + @"
                       ," + comm.to_sqltext(Content) + @"
                       ," + comm.to_sqltext(StartDate) + @"
                       ," + comm.to_sqltext(EndDate) + @"
                       ," + comm.to_sqlnumber(Status) + @"
                       ," + comm.to_sqlnumber(RoundAwards) + @"
                        )";
            try
            {
                DBUtil.Execute(sql);
                return 1;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        public static int EditRound(string Title, string Content, string StartDate, string EndDate, string Status, string RoundAwards, string ID)
        {
            //cap nhat
            string sql = @"UPDATE AwardsWeek
                         SET Title = " + comm.to_sqltext(Title) + @"
                        ,Content = " + comm.to_sqltext(Content) + @"
                        ,StartDate = " + comm.to_sqltext(StartDate) + @"
                        ,EndDate = " + comm.to_sqltext(EndDate) + @"
                        ,Status = " + comm.to_sqlnumber(Status) + @"
                        ,RoundAwards = " + comm.to_sqlnumber(RoundAwards) + @"
                         WHERE AwardsID=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sql);
                return 1;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        #region ảnh
        public static int UpdateImageThumbList(string ID, string url)
        {
            //xoa anh doat giai
            string sqlSetStatus = @"update Images set ImgThumbList= " + comm.to_sqltext(url) + " where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }
        public static int UpdateImageThumb(string ID,string url)
        {
            //xoa anh doat giai
            string sqlSetStatus = @"update Images set ImgThumb= " + comm.to_sqltext(url) + " where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }

        public static int UpdateVoteImagesByPoint(string ID,string Number)
        {
            //xoa anh doat giai
            string sqlSetStatus = @"update Images set likeWeb=likeWeb + " + comm.to_sqlnumber(Number) + " where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }
        public static int UpdateVoteImages(string ID)
        {
            //xoa anh doat giai
            string sqlSetStatus = @"update Images set likeWeb=likeWeb + 1 where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }

        public static int UpdateFacebookCount(string ID, string like,string share,string comment)
        {
            //xoa anh doat giai
            string sqlSetStatus = @"update Images 
                    set 
                    LikeFb=" + comm.to_sqlnumber(like) + @", 
                    CountComment=" + comm.to_sqlnumber(comment) + @",
                    CountShare=" + comm.to_sqlnumber(share) + @" 
                    where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }

        public static int EditStatusImages(string ID, string status)
        {
            //xoa anh doat giai
            string sqlSetStatus = "update Images set status=" + comm.to_sqlnumber(status) + " where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }
        public static int UpdateImageFile(string ID, string img)
        {
            //xoa anh doat giai
            string sqlSetStatus = "update Images set img=" + comm.to_sqltext(img) + " where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }
        public static int EditIsFocusImages(string ID, string isFocus)
        {
            //xoa anh doat giai
            string sqlSetStatus = "update Images set isFocus=" + comm.to_sqlnumber(isFocus) + " where ImageId=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }
        public static DataTable GetInfomationImages(string ID)
        {
            string sql = @"SELECT  * FROM Images where ImageId=" + comm.to_sqlnumber(ID);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getAllImagesOther(string name, string top, string ID)
        {
            //danh sach anh khac trang chi tiet
            DataTable dtInfoImages = GetInfomationImages(ID);
            string sql="";
            if (dtInfoImages.Rows.Count > 0)
            {
                string checkImgRound = dtInfoImages.Rows[0]["RoundAwards"].ToString();
                //tien hanh check
                string sqlCheckThuoc = "select * from AwardsImages where (status=1 or status =2) and ImageID=" + comm.to_sqlnumber(ID);
                DataTable dtCheckVongthuocvongthi = DBUtil.SelectTable(sqlCheckThuoc);
                int numCheck = dtCheckVongthuocvongthi.Rows.Count;
                if (numCheck > 0)
                {
                    string IdVongthi = dtCheckVongthuocvongthi.Rows[0]["AwardsID"].ToString();
                    //danh sach anh thuoc album nay
                    sql = @"select top " + top + @" im.ImageId,im.name,im.img,im.ImgThumb,im.title,im.LikeWeb,im.Birthday,im.ShortContent
                        from Images im inner join AwardsImages ai on im.ImageId=ai.ImageId
                        inner join AwardsWeek aw on ai.AwardsID = aw.AwardsID
                        where ai.status=1 and aw.RoundAwards=" + comm.to_sqlnumber(checkImgRound) + " and 1=1 and (im.status=1 or im.status =2) and im.ImageId != " + ID + " AND (im.Img IS NOT NULL AND im.ImgThumb IS NOT NULL AND im.ImgThumbList IS NOT null) order by im.ImageId desc";
                    // aw.AwardsID="+comm.to_sqlnumber(IdVongthi)+" and
                }
                else
                { 
                    //khong thi lay anh moi ra
                    sql = @"select top " + top + @" ImageId,name,img,ImgThumb,title,LikeWeb,Birthday,ShortContent
                        from Images where 1=1 and (status=1 or status =2) and ImageId != " + ID + " AND (Img IS NOT NULL AND ImgThumb IS NOT NULL AND ImgThumbList IS NOT null) order by ImageId desc";
                }
            }
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getAllImages(string search, string startDate, string endDate, string dkCount, string status, string round, string CheckShowTheoVong, string orderby)
        {
            //order by
            string dkOrder = " order by ImageId desc";
            if (orderby == "1")
            { 
                dkOrder = " order by LikeFb desc";
            }
            else if (orderby == "2")
            { 
                dkOrder = " order by CountComment desc";
            }
            else if (orderby == "3")
            { 
                dkOrder = " order by CountShare desc";
            }
            else if (orderby == "4")
            { 
                dkOrder = " order by LikeWeb desc";
            }
            //tim theo vong
            string dkRound = "";
            if (round != "0")
            {
                if (CheckShowTheoVong == "1")
                {
                    dkRound = "and RoundAwards=" + comm.to_sqlnumber(round) + " ";
                }
            }
            //tim theo status
            string dkStatus = "";
            if (status != "-1")
            {
                if (status == "1")
                {
                    dkStatus = "and status=1  ";
                }
                else
                {
                    dkStatus = "and status=" + comm.to_sqlnumber(status) + " ";
                }
            }
            //tim theo text
            string dkSearch = "";
            if (search != "")
            {
                dkSearch = "and name like N'%" + search + "%' ";
            }
            string sql = @"select ImgThumb,ImgThumbList,RoundAwards,CountComment,CountShare,likefb,likeweb,linkfb,birthday,ShortContent,Phone,Address,email,ImageId,name,img,title,CreateDate,PublicDate,status,case status
                        when 0 then N'Chưa duyệt' 
                        when 1 then N'Duyệt' 
                        when 2 then N'Trao giải'
                        when 3 then N'Khóa lại' end as trangthai
                        ,case IsFocus
                        when 0 then N'Ảnh bình thường' 
                        when 1 then N'Tiêu điểm ảnh' end as tieudiem
                        from Images where 1=1" + dkRound + dkStatus + dkSearch + dkOrder;
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getAllImagesDoatGiai(string search, string startDate, string endDate, string dkCount, string status, string round, string giaiID)
        {
            //tim theo vong
            string dkRound = "";
            if (round != "0")
            {
               // dkRound = "and im.RoundAwards=" + comm.to_sqlnumber(round) + " ";
            }
            //tim theo status
            string dkStatus = "";
            if (status != "-1")
            {
                if (status == "1")
                {
                    dkStatus = "and im.status=1 ";
                }
                else
                {
                    dkStatus = "and im.status=" + comm.to_sqlnumber(status) + " ";
                }
            }
            //tim theo text
            string dkSearch = "";
            if (search != "")
            {
                dkSearch = "and im.name like N'%" + search + "%' ";
            }
            //giai thuong
            string dkgiaiID = "";
            if (giaiID != null || giaiID != "")
            {
                dkgiaiID = " and aw.AwardsID=" + comm.to_sqlnumber(giaiID) + " ";
            }
            string sql = @"select im.RoundAwards,im.CountComment,im.CountShare,im.likefb,im.likeweb,im.linkfb,im.birthday,ai.ID,im.ShortContent,im.Phone,im.Address,im.email,im.ImageId,im.name,im.img,im.title,im.CreateDate,im.PublicDate,im.status,case im.status
                        when 0 then N'Chưa duyệt' 
                        when 1 then N'Duyệt' 
                        when 2 then N'Trao giải'
                        when 3 then N'Khóa lại' end as trangthai
                        ,case im.IsFocus
                        when 0 then N'Ảnh bình thường' 
                        when 1 then N'Tiêu điểm ảnh' end as tieudiem
                        ,case ai.Status
                        when 0 then N'Bỏ đoạt giải' 
                        when 1 then N'Set đoạt giải' end as setdoatgiai
                        from Images im inner join AwardsImages ai on im.ImageId=ai.ImageId
                        inner join AwardsWeek aw on ai.AwardsID=aw.AwardsID
                        where 1=1" + dkgiaiID + dkRound + dkStatus + dkSearch + " order by im.ImageId desc";
            return DBUtil.SelectTable(sql);
        }
        #endregion

        #region set anh doat giai
        public static int addImgDoatGiai(string ImageId, string AwardsID)
        {
            string sql = "insert into AwardsImages(ImageId,AwardsID,Status) values(" + comm.to_sqlnumber(ImageId) + "," + comm.to_sqlnumber(AwardsID) + ",1)";
            string sqlSetStatus = "update Images set status=2 where ImageId=" + comm.to_sqlnumber(ImageId);
            try
            {
                DBUtil.Execute(sql);
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                string ms = ex.Message;
                if (ms.ToLower().Contains("duplicate key"))
                {
                    return -1;//da co ban ghi nay
                }
                else
                {
                    return 0;//that bai
                }
            }
        }
        public static DataTable GetAllAwards(string RoundAwards)
        {
            string sql = "select AwardsID,Title from AwardsWeek where Status > 0 and Status !=4 and RoundAwards=" + comm.to_sqlnumber(RoundAwards);
            return DBUtil.SelectTable(sql);
        }
        public static int EditStatusAnhDoatGiai(string ID, string status)
        {
            //xoa anh doat giai
            string sqlSetStatus = "update AwardsImages set status=" + comm.to_sqlnumber(status) + " where ID=" + comm.to_sqlnumber(ID);
            try
            {
                DBUtil.Execute(sqlSetStatus);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;//that bai
            }
        }
        #endregion

    }
}
